/*
** 
** Author: Tsihang, Beijing, China
** Time: 23:00, September 22, 2011      
** 
** This C file is prepared for Sqlite3 database testing. Some API(S) are surpplied 
** by the author. More information wanted, you can email to hang.qi@chinacache.com  
**
** int sqlite3_open(const char * basename, sqlite3 *database) to open a database that named "basename",
       and pointed by sqlite3 *;
**
** int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**) to execute the sql;
**
** const char *sqlite3_errmsg(sqlite3*) to deal the error messge;
**   
** int sqlite3_close(sqlite3 *database) to close a database.
**
*/

#include <stdio.h>
#include <string.h>
#include <sqlite3.h>

#define _DBHDL (sqlite3 * _db)

#define DB "../database/user.db"

/* We can use this defination to debug our own function */
#define _TABLE_
#define _SELECT_
//#define _INSERT_
//#define _DELETE_
#define _DEBUGS_
#define _CALLBACK_
#define SIZE 256 
char sql_cmd[256];

sqlite3 *_db;

#ifdef _TABLE_
#define OPENSQLITE3 \
	_openSqlite3()
#define CLOSESQLITE3 \
	_closeSqlite3()

#define PRINTK \
	printf("Database Closed\n");

/* open a database, if there is not one, creates it then */
int _openSqlite3(void) { return sqlite3_open(DB, &_db); }
/* close a database */
int _closeSqlite3(void) { sqlite3_close(_db); PRINTK; return 0;}
#endif 

#ifdef _CALLBACK_
int sqlite_callback(char * sql, sqlite3 *_db)
{
	char**result = 0;
	int nrow = 0;
	int ncolumn = 0;
	char *zeMsg = NULL;
	int i, j;

	sqlite3_get_table(_db, sql , &result , &nrow , &ncolumn, &zeMsg);
	printf("%d, %d\n", nrow, ncolumn);
	for(i = 0; i < (nrow + 1); i++)
	{
		//printf("%d.", i) ; 
		printf( "%d. %s", i, result[i]);
	}
	/* free the space that hold by "result" */
	sqlite3_free_table(result);
    return 0;
}

int query_callback(void* p, int argc, char** argv, char** argvv)
{
    (*(int*)p)++;
	int i;
	for(i = 0; i < argc; i++)
		printf("%s = %s  ", argvv[i], argv[i] ? argv[i]: "NULL");
	printf("\n");
    return 0;
}
#endif

#ifdef _SELECT_
int _selectSqlite3(void)
{
	char *eMsg = NULL;
	extern sqlite3 *_db;
    int iret;
    int number;
/* Open db */
	if(OPENSQLITE3)
	{
		printf("Failed to open database %s\n", DB);
		CLOSESQLITE3;
		return 1;
	}
    printf("Selecting...\n");
	sprintf(sql_cmd, "select * from member;");

/* Execute SQL for select database */
	iret = sqlite3_exec(_db, sql_cmd, query_callback, &number, &eMsg);
    
#ifdef _DEBUGS_
    if(iret == SQLITE_OK && eMsg == NULL)
        printf("%s: Yes!\n", sql_cmd);
    
    else
        printf("%s: %s!\n", sql_cmd, eMsg);
#endif
    /* No matter what we must close database handle after uses it ! It may be an order!*/
	CLOSESQLITE3;
    
	return 0;
}
#endif

#ifdef _INSERT_ 
int _insertSqlite3(const char * name,  const char * sex, const char * age, const char * addr)
{
    char *eMsg = NULL;
    extern sqlite3 *_db;
    int iret = 10;
    int number;
    /* Open db */
	if(OPENSQLITE3)
	{
		printf("Failed to open database %s\n", DB);
		CLOSESQLITE3;
		return 1;
	}
    printf("Inserting...\n");
    sprintf(sql_cmd, "insert into member values(\'%s\', \'%s\', \'%s\', \'%s\');", name, sex, age, addr);   
    /* Execute SQL for select database */
	iret = sqlite3_exec(_db, sql_cmd, query_callback, &number, &eMsg);

#ifdef _DEBUGS_
	sprintf(sql_cmd, "%s %s %s %s", name, sex, age, addr);
	if(iret == SQLITE_OK && eMsg == NULL)
	  printf("%s: Yes\n", sql_cmd);
	else
	   printf("%s: %s\n", sql_cmd, eMsg);
#endif
    /* No matter what we must close database handle after uses it ! It may be an order!*/
	CLOSESQLITE3;
    
	return 0;
}
#endif

#ifdef _DELETE_
int _deleteSqlite3(const char* values)
{
	char *eMsg = NULL;
	extern sqlite3 *_db;
    int iret = 10;
    int number;
    /* Open db */
	if(OPENSQLITE3)
	{
		printf("Failed to open database %s\n", DB);
		CLOSESQLITE3;
		return 1;
	}
    printf("Deleting...\n");
    
	sprintf(sql_cmd, "delete from member where name=\'%s\';", values);   
    /* Execute SQL for select database */
	iret = sqlite3_exec(_db, sql_cmd, query_callback, &number, &eMsg);
#ifdef _DEBUGS_    
    if(iret == SQLITE_OK && eMsg == NULL)
        printf("%s: Yes!\n", sql_cmd);
    else
        printf("%s: %s!\n", sql_cmd, eMsg);
#endif
    /* No matter what we must close database handle after uses it ! It may be an order!*/
	CLOSESQLITE3;
    
	return 0;
}
#endif
    
/* Main function */
int main(int argc, char * argv[])
{
	printf("Exe database named \"../database/user.db\"\n");
	printf("Wating ...\n");
	printf("\n");
#ifdef _SELECT_
	printf("Select result is: \n");
	printf("---------------------------------------\n");
	    _selectSqlite3();
	printf("---------------------------------------\n");
#endif
	
#ifdef _INSERT_
	printf("\n");
	printf("Insert result is: \n");
	printf("---------------------------------------\n");
	    _insertSqlite3(argv[1], argv[2], argv[3], argv[4]);
	printf("---------------------------------------\n");
#endif

#ifdef _DELETE_
	printf("\n");
	printf("Delete result is: \n");
	printf("---------------------------------------\n");
    	    _deleteSqlite3(argv[1]);
	printf("---------------------------------------\n");
#endif
	return 0;
}
